using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using DTO;
//hien
namespace DAO
{
    public class BacSiKeToaDao : IDaoObject<BacSiKeToaDto, int>
    {
        public BacSiKeToaDto SaveOrUpdate(BacSiKeToaDto obj)
        {
            return SaveOrUpdate(obj, null);
        }

        public BacSiKeToaDto SaveOrUpdate(BacSiKeToaDto obj, OleDbTransaction transaction)
        {
            OleDbCommand cmd = new OleDbCommand();
            if (transaction != null)
            {
                cmd.Connection = transaction.Connection;
                cmd.Transaction = transaction;
            }
            else
            {
                cmd.Connection = MyConnection.ConnectionData();
            }
            
            string strSql = "";
            int id = 0;
            if (obj.Id > 0 || CheckAlready(obj.TenBacSiKeToa, out id))
            {
                //No need to update table 'cause there's no more data field
                if (obj.Id <= 0) obj.Id = id;
                return obj;
            }
            //Insert INTO Loaiduocpham table
            strSql = "Insert into BSketoa (Tenbacsiketoa) values ('" + obj.TenBacSiKeToa + "')";

            cmd.CommandText = strSql;
            cmd.ExecuteNonQuery();
            strSql = "Select @@IDENTITY";
            cmd.CommandText = strSql;
            cmd.Transaction = transaction;
            obj.Id = (int)cmd.ExecuteScalar();
            return obj;
        }

        private static bool CheckAlready(string name, out int id)
        {
            id = 0;
            OleDbConnection con = MyConnection.ConnectionData();
            string sql = "select ID from BSketoa where Tenbacsiketoa='" + name + "'";
            OleDbDataAdapter dta = new OleDbDataAdapter(sql, con);
            DataTable dataTable = new DataTable();
            dta.Fill(dataTable);
            if (dataTable.Rows.Count > 0)
            {
                id = Convert.ToInt32(dataTable.Rows[0]["ID"].ToString());
            }
            return id > 0;
        }

        public void Delete(int id)
        {
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSQL = "Delete From Hinhthucban where Hinhthucban.BacsiketoaiID='" + id + "' Delete From BSketoa Where Id = ? ";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Id", OleDbType.Integer);
            cmd.Parameters["@Id"].Value = id;
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        public BacSiKeToaDto Select(int id)
        {
            DataTable dt = new DataTable();
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSQL = "Select * From BSketoa where ID='" + id + "'";
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            da.Fill(dt);
            cn.Close();
            BacSiKeToaDto dto = new BacSiKeToaDto();
            DataRow row = dt.Rows[0];
            dto.Id = Convert.ToInt32(row["ID"].ToString());
            dto.TenBacSiKeToa = (string)row["Tenbacsiketoa"];
            return dto;
        }

        public BacSiKeToaDto[] SelectAll()
        {
            //throw new NotImplementedException();
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSQL = "select * from BSketoa ";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            List<BacSiKeToaDto> ldpdto = new List<BacSiKeToaDto>();
            while (dr.Read())
            {
                BacSiKeToaDto ldp = new BacSiKeToaDto();
                ldp.Id = Convert.ToInt32(dr["ID"].ToString());
                ldp.TenBacSiKeToa = dr["Tenbacsiketoa"].ToString();
                ldpdto.Add(ldp);
            }
            dr.Close();
            cn.Close();
            return ldpdto.ToArray();
        }

        public BacSiKeToaDto [] SelectBacsiketoa(DateTime tngay, DateTime dngay)
        {
            //throw new Exception("The method or operation is not implemented.");
            try
            {
                OleDbConnection cn = MyConnection.ConnectionData();
                string strSql =
                    "SELECT tien.Tenbacsiketoa, Sum(tien.Tongsotienthanhtoan) AS SumOfTongsotienthanhtoan, Sum(toa.CountOfID) AS SumOfCountOfID" +
                    " FROM View_Tongsotien_Toabacsi AS tien INNER JOIN View_Tongsotoa_Toabacsi AS toa ON (tien.ID = toa.ID) AND (tien.Ngaytao = toa.Ngaytao)" +
                    " WHERE tien.Ngaytao <= #" + dngay + "# And tien.Ngaytao >= #" + tngay + "#" +
                    " GROUP BY tien.Tenbacsiketoa;";

                
                OleDbCommand cmd = new OleDbCommand(strSql, cn);
                OleDbDataReader dr = cmd.ExecuteReader();
                List<BacSiKeToaDto> list = new List<BacSiKeToaDto>();

                while (dr.Read())
                {
                    BacSiKeToaDto bskt = new BacSiKeToaDto();
                    //HoaDonBanDto hdb = new HoaDonBanDto();

                    // htb.Id = Convert.ToInt32(dr["ID"].ToString());
                    bskt.TenBacSiKeToa = dr["Tenbacsiketoa"].ToString();
                    bskt.TongSoToa = Int32.Parse( dr["SumOfCountOfID"].ToString());
                    bskt.Tongsotienthanhtoan = dr["SumOfTongsotienthanhtoan"].ToString();

                    list.Add(bskt);
                }
                dr.Close();
                cn.Close();
                return list.ToArray();
            }
            catch (Exception exception)
            {

                throw new Exception(exception.Message);
            }


        }
    }
}
